--EXEC SP_AVAILABLE_LOTS_FOR_TRANSFER 'Pre Test', 0, 0, 0
-- NEEDS TO MODIFY FOR WAFER QTY IN
	
ALTER PROC SP_AVAILABLE_LOTS_FOR_TRANSFER
( 
	@PROCESS_TYPE   VARCHAR(50),
	@PROCESSSTEP_ID INT,
	@LOCATION_ID INT,
	@SEARCH_LOT_ID INT
)
AS
BEGIN
	SET NOCOUNT ON
	CREATE TABLE #RESULTS ( LOT_ID INT, PROCESSSTEP_ID INT, LOCATION_ID INT, PART_NUMBER VARCHAR(50), Lot_Number VARCHAR(50), 
			Fab_Lot_Number VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT, DATE_IN DATETIME, VENDOR_NAME VARCHAR(50), DATE_CODE VARCHAR(50), 
			PS_TYPE VARCHAR(50), PS_ORDER INT, WAFER_IDS VARCHAR(200), LOT_STATUS VARCHAR(50), LOT_COMMENTS VARCHAR(500) ) 
	
	DECLARE @QUERYSTRING	VARCHAR(4000)

	SET @QUERYSTRING = N'
 	INSERT INTO #RESULTS 
 	SELECT	LT.LOT_ID, LT.PROCESSSTEP_ID, LT.LOCATION_ID, PART_NUMBER, LOT_NUMBER, FAB_LOT_NUMBER, WAFER_QTY_IN, QTY_IN, DATE_IN, 
	L_VENDOR_NAME, LOT_DATE_CODE, PS.PS_Type, PTT.Sorting_Order, NULL, LOT_CURRENT_STATUS, LOT_COMMENTS
	FROM LOT_TABLE LT, PROCESS_STEP PS, LOCATION L, PRODUCT P, Process_Types_Table PTT 
	WHERE 
	LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_Type = PTT.Process_Type AND
	LT.LOCATION_ID = L.LOCATION_ID AND ( WAFER_QTY_IN > 0 OR QTY_IN > 0 ) AND LT.LOT_CURRENT_STATUS <> ''Close'' '
	
		IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	 	BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING + ' AND PS_TYPE = '''+ @PROCESS_TYPE+''''
	 	END
	 	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID > 0  )
	 	BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING +' AND PS.PROCESSSTEP_ID = ' + CONVERT(VARCHAR,@PROCESSSTEP_ID)
	 	END
		IF ( @LOCATION_ID IS NOT NULL AND @LOCATION_ID > 0  )
		BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING +' AND L.Vendor_ID = ' + CONVERT(VARCHAR,@LOCATION_ID)
	 	END
	 	IF ( @SEARCH_LOT_ID IS NOT NULL AND @SEARCH_LOT_ID > 0  )
		BEGIN
 			SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOT_ID = ' + CONVERT(VARCHAR,@SEARCH_LOT_ID)
 		END

		SET @QUERYSTRING = @QUERYSTRING + ' ORDER BY P.PART_NUMBER'
	 EXEC (@QUERYSTRING)

	DECLARE @WIPCURSOR CURSOR
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @WAFER_IDS VARCHAR(100)
	DECLARE @LOT_ID INT
	
	SET @WIPCURSOR = CURSOR FOR SELECT LOT_ID, PS_TYPE FROM #RESULTS WHERE PS_ORDER <= (SELECT Sorting_Order FROM Process_Types_Table WHERE Process_Type = 'Die Bank' )
		
	OPEN @WIPCURSOR
	FETCH NEXT FROM @WIPCURSOR INTO @LOT_ID, @PS_TYPE 
	WHILE @@FETCH_STATUS = 0
	BEGIN
			SELECT @PS_TYPE_NAME = BASE_PROCESS_TYPE FROM Process_Types_Table WHERE Process_Type = @PS_TYPE

			IF @PS_TYPE_NAME IS NOT NULL AND @PS_TYPE_NAME = 'FAB'
			BEGIN
					SELECT @WAFER_IDS = Wafer_Id FROM Fab_Lot WHERE Lot_ID = @LOT_ID
			END
			ELSE IF @PS_TYPE_NAME IS NOT NULL AND ( @PS_TYPE_NAME = 'Wafer Store' OR @PS_TYPE_NAME = 'Die Bank' )
			BEGIN
					SELECT @WAFER_IDS = Wafer_Id FROM Inventory_Lot WHERE Lot_ID = @LOT_ID
			END
			ELSE IF @PS_TYPE_NAME IS NOT NULL AND ( @PS_TYPE_NAME = 'Sort' )
			BEGIN
					SELECT @WAFER_IDS = SL_Wafer_Id FROM Sort_Lot WHERE Lot_ID = @LOT_ID
			END
			
		UPDATE #RESULTS SET WAFER_IDS = @WAFER_IDS WHERE LOT_ID = @LOT_ID
		SET @PS_TYPE	= NULL
		SET @LOT_ID		= NULL
		SET @WAFER_IDS		= NULL
		SET @PS_TYPE_NAME		= NULL
		
		FETCH NEXT FROM @WIPCURSOR INTO @LOT_ID, @PS_TYPE 
		
	END
	CLOSE @WIPCURSOR
	DEALLOCATE @WIPCURSOR

	SELECT 
	 LOT_ID ,PROCESSSTEP_ID , LOCATION_ID , PART_NUMBER , Lot_Number , Fab_Lot_Number , WAFER_QTY_IN, QTY_IN , CONVERT(VARCHAR,DATE_IN, 101) AS DATE_IN , VENDOR_NAME, DATE_CODE, WAFER_IDS, LOT_STATUS,LOT_COMMENTS 
	FROM #RESULTS ORDER BY LOT_NUMBER
END